package com.limetalks.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class DAO {
	
	private Connection conn = null;

	private void connect() {

	    try {
	        String userName = "root";
	        String password = "mysql";
	        String url = "jdbc:mysql://localhost/lime";
	        Class.forName ("com.mysql.jdbc.Driver").newInstance ();
	        conn = DriverManager.getConnection (url, userName, password);
	        System.out.println ("Database connection established");
	    } catch (Exception e) {
	         System.err.println ("Cannot connect to database server");
	    } 
	}
	
	public boolean checkUser(String user, String password) {
		
		connect();
		String sql = "select id from user where login = ? and password = ?";
		try {
			 PreparedStatement ps = conn.prepareStatement(sql);
			 ps.setString(1, user);
			 ps.setString(2, password);
			 ResultSet rs = ps.executeQuery();
			 return rs.next();
			 
		 } catch (Exception e) {
			 System.out.println ("Database connection terminated");
			 e.printStackTrace();
		 } finally {
			 closeConnection();
		 }
		 return false;
	}
	 
	 public void addUser(String email, String login, String password, String code) {
		 
		 connect();		 
		 String sql = "insert into user (email, login, password, status, code)  values (?,?,?,'0',?)";
		 try {
			 PreparedStatement ps = conn.prepareStatement(sql);
			 ps.setString(1, email);
			 ps.setString(2, login);
			 ps.setString(3, password);
			 ps.setString(4, code);
			 ps.execute();
			 ps.close();
		 } catch (Exception e) {
			 System.out.println ("Database connection terminated");
			 e.printStackTrace();
		 } finally {
			 closeConnection();
		 }
	 }
	 
	 public boolean confirmReg(String code) {
		 
		 connect();
		 String sql = "update user set status = '1' where code = ?";
		 try {
			 PreparedStatement ps = conn.prepareStatement(sql);
			 ps.setString(1, code);
			 ps.execute();
			 ps.close();
			 return true;
		 } catch (Exception e) {
			 System.out.println ("Database connection terminated");
			 e.printStackTrace();
		 } finally {
			 closeConnection();
		 }
		 return false;
	 }
	 
	 public static void main(String[] args) throws SQLException {
		DAO d = new DAO();
		//d.addUser("aaa","bbb","ccc","eeee");
		//System.out.println(d.checkUser("Vova1", "a"));
		for(String s : d.getCategories())
			System.out.println(s);
	}
	 
	private boolean closeConnection() {
		try {
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public String[] getCategories() {
		
		connect();
		String sql = "select title from category";
		try {
			
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			
			rs.last();  
			String[] categories = new String[rs.getRow()];
			
			rs.beforeFirst();
			int i = 0;
			while(rs.next()) {
				categories[i] = rs.getString("title");
				i++;
			}
			ps.close();
			return categories;
		} catch (Exception e) {
			System.out.println ("Database connection terminated");
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		return null;
	}

}
